package com.dick.base.util;

import com.dick.base.exception.BaseRuntimeException;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.springframework.http.HttpStatus;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

@Slf4j
public class ExcelUtil {

    /**
     * 解析订单excel
     * @param file
     * @return
     * @throws Exception
     */
//    public static OrderDetail parseOrder(MultipartFile file) throws Exception{
//        boolean classic = isClassicExcelType(file);
//        OrderDetail detail = new OrderDetail();
//        Workbook workbook = null;
//        try {
//            BusinessOrder order = new BusinessOrder();
//            detail.setOrder(order);
//            workbook = classic ? new HSSFWorkbook(file.getInputStream()) : new XSSFWorkbook(file.getInputStream());
//            Sheet sheet = workbook.getSheetAt(0);
//            Row row = sheet.getRow(2);
//            order.setSendCompany(getValueWithoutTitle(row.getCell(0)));
//            String dateFormatString = getValueWithoutTitle(row.getCell(6));
//            if (StringUtils.isEmpty(dateFormatString)) {
//                throw new BaseRuntimeException("请在G3单元格内填入正确的销售日期，格式为____年__月__日");
//            }
//            order.setSaleDate(
//                    DateUtil.parseDate(dateFormatString, "yyyy年MM月dd日")
//            );
//            row = sheet.getRow(3);
//            order.setAddress(getValueWithoutTitle(row.getCell(1)));
//            String materialPriceString = getValueWithoutTitle(row.getCell(6));
//            if (StringUtils.isEmpty(materialPriceString)) {
//                throw new BaseRuntimeException("请在G4单元内填入正确的原材料单价，单位为元/吨");
//            }
//            order.setMaterialPrice(
//                    (int) Double.parseDouble(materialPriceString)
//            );
//            order.setOrderNumber(
//                    getValueWithoutTitle(row.getCell(8))
//            );
//            List<BusinessOrderItem> itemList = new LinkedList<>();
//            for (int i = 5, itemIndex = 0; i < 5000; i ++) {
//                row = sheet.getRow(i);
//                if (row == null) {
//                    break;
//                }
//                String material = getSingleCellStringValue(row.getCell(0));
//                if (!StringUtils.isEmpty(material)) {  //属于数据行-item
//                    BusinessOrderItem item = new BusinessOrderItem();
//                    item.setItemIndex(itemIndex ++);
//                    item.setMaterial(material);
//                    item.setName(getSingleCellStringValue(row.getCell(1)));
//                    item.setThickness(getSingleCellStringValue(row.getCell(2)));
//                    item.setWidth(parseInt(row.getCell(3)));
//                    item.setLength(parseInt(row.getCell(4)));
//                    item.setQuantity(parseInt(row.getCell(5)));
//                    itemList.add(item);
//                } else {  //不是数据行
//                    String heji = getSingleCellStringValue(row.getCell(3));
//                    if ("合计".equals(heji)) {  //是合计行
//                        BusinessOrderItems items = new BusinessOrderItems();
//                        items.setQuantity(itemList.stream().mapToInt(BusinessOrderItem::getQuantity).sum());
//                        items.setPrice(parseFen(row.getCell(7)));
//                        items.setShelf(getSingleCellStringValue(row.getCell(2)));
//                        items.setWeight(parseInt(row.getCell(6)));
//                        items.setTotalPrice(parseFen(row.getCell(8)));
//                        items.setId(IdUtil.nextId());
//                        detail.getItems().add(items);
//                        detail.getItemMap().put(items.getId(), new ArrayList<>(itemList));
//                    } else if ("总计".equals(heji)) {
//                        order.setQuantity(parseInt(row.getCell(5)));
//                        order.setWeight(parseInt(row.getCell(6)));
//                        order.setTotalPrice(parseFen(row.getCell(8)));
//                        break;
//                    }
//                    itemIndex = 0;
//                    itemList.clear();
//                }
//            }
//        } finally {
//            if (workbook != null) {
//                try {
//                    workbook.close();
//                } catch (Exception e) {
//                    log.error("parse order excel failed!", e);
//                }
//            }
//        }
//        return detail;
//    }

    /**
     * 把金额为元的小数解析成单位为分的整数
     * @param cell
     * @return
     */
    private static Integer parseFen(Cell cell) {
        return (int) Math.round(parseDouble(cell) * 100);
    }

    private static Double parseDouble(Cell cell) {
        String value = getSingleCellStringValue(cell);
        if (StringUtils.isEmpty(value)) {
            return 0d;
        }
        try {
            return Double.parseDouble(value);
        } catch (NumberFormatException numberFormatException) {
            throw new BaseRuntimeException("表格中存在无法解析成数字的单元格！请检查表格！");
        }
    }

    private static Integer parseInt(Cell cell) {
        String value = getSingleCellStringValue(cell);
        if (StringUtils.isEmpty(value)) {
            return 0;
        }
        try {
            return Integer.parseInt(value);
        } catch (NumberFormatException numberFormatException) {
            throw new BaseRuntimeException("表格中存在无法解析成整数的单元格！请检查表格！");
        }
    }

    private static String getSingleCellStringValue(Cell cell) {
        String value = "";
        if (cell == null) {
            return value;
        }
        switch (cell.getCellType()) {
            case NUMERIC:
            case FORMULA:
                double v = cell.getNumericCellValue();
                if (v == Math.round(v)) {
                    value = String.valueOf(Math.round(v));
                } else {
                    value = String.valueOf(v);
                }
                break;
            case STRING:
            case BLANK:
                value = cell.getStringCellValue();
                break;
            case ERROR:
                value = String.valueOf(cell.getErrorCellValue());
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue() ? "对" : "错";
                break;
            case _NONE:
                break;
        }
        if (value != null) {
            value = value.trim();
        } else {
            value = "";
        }
        return value;
    }

    private static String getValueWithoutTitle(Cell cell) {
        String string = getSingleCellStringValue(cell);
        int index = string.indexOf(":");
        if (index == -1) {
            index = string.indexOf("：");
        }
        if (index == -1) {
            return "";
        }
        return string.substring(index+1).replaceAll("\\s", "");
    }

    /**
     * 根据文件后缀名判断是传统xls格式还是xlsx格式
     * @param file
     * @return true-传统格式；false-新格式
     */
    private static boolean isClassicExcelType(MultipartFile file) {
        String name = file.getOriginalFilename();
        if (name.endsWith(".xls") || name.endsWith(".XLS")) {
            return true;
        } else if (name.endsWith(".xlsx") || name.endsWith(".XLSX")) {
            return false;
        } else {
            throw new BaseRuntimeException("仅支持.xls和.xlsx格式的表格", HttpStatus.UNSUPPORTED_MEDIA_TYPE);
        }
    }
}
